package com.asohofrucol.dao;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.asohofrucol.model.Ruat;

public interface RuatDao extends BasicSaveDao<Ruat, Long> {

	@Query("SELECT p FROM Ruat p WHERE p.idNumber = :idNumber")
	public List<Ruat> findByIdNumber(@Param("idNumber") String idNumber);

	@Query("SELECT p FROM Ruat p WHERE p.status not in ('-1') and ((p.comboValueIdType.id = :idType and p.idNumber like CONCAT('%',:idNumber,'%')) or p.associateName like CONCAT('%',:name,'%'))")
	public List<Ruat> findByIdTypeIdNumberOrName(@Param("idType") Long idType,
			@Param("idNumber") String idNumber, @Param("name") String name);

	@Query("SELECT p FROM Ruat p WHERE p.comboValueIdType.id = :idType and p.idNumber like CONCAT('%',:idNumber,'%')")
	public List<Ruat> findByIdTypeIdNumber(@Param("idType") Long idType,
			@Param("idNumber") String idNumber);

	@Query("SELECT COUNT(p) FROM Ruat p WHERE p.comboValueIdType.id = :idType and p.idNumber like CONCAT('%',:idNumber,'%')")
	public Long countByIdTypeIdNumber(@Param("idType") Long idType,
			@Param("idNumber") String idNumber);

	// para este caso deben ir varios 'or' dependiendo de los campos de la tabla
	// que admitan filtro, se debe separar los nombres y apellidos o se debe
	// crear un
	// atributo concatenado a la entidad ejm: fullName = firstLastName ||
	// secondLastName
	@Query("SELECT p FROM Ruat p WHERE (p.fullName like CONCAT('%',:fullName,'%') or  p.fullLastName like CONCAT('%',:fullLastName,'%') or  p.idNumber like CONCAT('%',:idNumber,'%')) and p.status  not in ('-1')")
	public Page<Ruat> findAll(@Param("fullName") String fullName,
			@Param("fullLastName") String fullLastName,
			@Param("idNumber") String idNumber, Pageable pageable);
}
